<?php
 
/**
 * Classe : Offre
 * @author Jeremy
 * @author Remi
 * @version 1.0
 */
class Offre
{

    /**
     * @var int
     */
    protected $Id;
    /**
     * @var int
     */
    protected $IdContactEntreprise;
    /**
     * @var int
     */
    protected $IdDomaineApplication;
    /**
     * @var int
     */
    protected $NombrePoste;
    /**
     * @var int
     */
    protected $NombrePosteDispo;
    /**
     * @var int
     */
    protected $DateCreation;
    /**
     * @var int
     */
    protected $DateDebut;
    /**
     * @var int
     */
    protected $DateFin;
    /**
     * @var array
     */
    protected $IdsCategorie;
    /**
     * @var array
     */
    protected $IdsTechnologie;
    /**
     * @var array
     */
    protected $IdsPromo;
    /**
     * @var string
     */
    protected $Nom;
    /**
     * @var string
     */
    protected $Description;
    /**
     * @var string
     */
    protected $Competence;
    /**
     * @var string
     */
    protected $PathPdf;
    /**
     * @var string
     */
    protected $Statut;
    /**
     * @var string
     */
    protected $Type;
    /**
     * @var bool
     */
    public $requeteOk;
    /**
     * @var string
     */
    public $errormsg;

    /**
     * Constructeur
     * @param int $idO
     */
    public function __construct($idO = 0)
    {

        // Traitement
        if (empty($idO))
        {
            // On retourne un objet initialise
            $this->Id = 0;

            //ID
            $this->IdContactEntreprise = 0;
            $this->IdDomaineApplication = 0;

            $this->IdsCategorie = array();
            $this->IdsTechnologie = array();

            // String
            $this->Nom = "";
            $this->Description = "";
            $this->Competence = "";
            $this->PathPdf = "";

            // Int
            $this->NombrePoste = 0;
            $this->NombrePosteDispo = 0;

            // Timestamp (int)
            $this->DateDebut = 0;
            $this->DateFin = 0;
            $this->DateCreation = 0;

            // Enum
            $this->Statut = "";
            $this->Type = "";
        }
        else
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;

            //Instanciation connexion PDO
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            //On retourne l'entree demandee
            $query = 'SELECT Offre.* FROM Offre WHERE Offre.IdOffre = :id';
            // Technologie
            $query2 = "SELECT IdCritere FROM CritereOffreRS WHERE IdTypeCritere = 3 AND IdOffre = :id";
            // Categorie
            $query3 = "SELECT IdCritere FROM CritereOffreRS WHERE IdTypeCritere = 4 AND IdOffre = :id";
            // Promo
            $query4 = "SELECT IdCritere FROM CritereOffreRS WHERE IdTypeCritere = 1 AND IdOffre = :id";


            $c = $sql->prepare($query);
            $c->bindParam(':id', $idO, PDO::PARAM_INT);

            // Execution de la requete retourne false si echec
            if ($c->execute())
            {
                $result = $c->fetch(PDO::FETCH_OBJ);
                if (!empty($result))
                {
                    $this->Id = $result->IdOffre;

                    //ID
                    $this->IdContactEntreprise = $result->IdContactEntreprise;
                    $this->IdDomaineApplication = $result->IdDomaineApplication;

                    // String
                    $this->Nom = $result->TitreOffre;
                    $this->Description = $result->DescriptionOffre;
                    $this->Competence = $result->CompetenceOffre;
                    $this->PathPdf = $result->PathPdfOffre;

                    // Int
                    $this->NombrePoste = $result->NombrePosteOffre;
                    $this->NombrePosteDispo = $result->NombrePosteDispoOffre;

                    // Timestamp (int)
                    $this->DateDebut = $result->DateDebutOffre;
                    $this->DateFin = $result->DateFinOffre;
                    $this->DateCreation = $result->DateCreationOffre;

                    // Enum
                    $this->Statut = $result->StatutOffre;
                    $this->Type = $result->TypeOffre;

                    // Gestion erreur requete
                    $this->requeteOk = true;
                    $this->errormsg = "Offre trouve";

                    // RECUPERATION CATEGORIES
                    $c3 = $sql->prepare($query3);
                    $c3->bindParam(':id', $idO, PDO::PARAM_INT);
                    if ($c3->execute())
                    {
                        $categories = $c3->fetchAll(PDO::FETCH_OBJ);
                        $this->IdsCategorie = array();
                        foreach ($categories as $cat)
                        {
                            array_push($this->IdsCategorie, $cat->IdCritere);
                        }
                    }
                    else
                    {
                        $this->requeteOk = false;
                        $this->errormsg = "Categories null";
                    }

                    // RECUPERATION Techno
                    $c2 = $sql->prepare($query2);
                    $c2->bindParam(':id', $idO, PDO::PARAM_INT);
                    if ($c2->execute())
                    {
                        $technos = $c2->fetchAll(PDO::FETCH_OBJ);
                        $this->IdsTechnologie = array();
                        foreach ($technos as $techno)
                        {
                            array_push($this->IdsTechnologie, $techno->IdCritere);
                        }
                    }
                    else
                    {
                        $this->requeteOk = false;
                        $this->errormsg = "Categories null";
                    }

                    // RECUPERATION promos
                    $c4 = $sql->prepare($query4);
                    $c4->bindParam(':id', $idO, PDO::PARAM_INT);
                    if ($c4->execute())
                    {
                        $promos = $c4->fetchAll(PDO::FETCH_OBJ);
                        $this->IdsPromo = array();
                        foreach ($promos as $promo)
                        {
                            array_push($this->IdsPromo, $promo->IdCritere);
                        }
                    }
                    else
                    {
                        $this->requeteOk = false;
                        $this->errormsg = "Categories null";
                    }
                }
                else
                {
                    $this->requeteOk = false;
                    $this->errormsg = "Cette Offre n'existe pas !";
                }
            }
            else
            {
                $this->requeteOk = false;
                $this->errormsg = $c->errorInfo();
            }
        }
    }

    /**
     * Ajoute l'offre dans la bdd et ajoute les relations avec Techonologie et Categorie
     * @see Categorie
     * @see Technologie
     * @see DomaineApplication
     */
    public function insertOffre()
    {
        //Recuperation des variables globales
        global $global_dsn;
        global $global_dsnUser;
        global $global_dsnPwd;
        //Instanciation connexion PDO
        $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
        $query =
                'INSERT INTO Offre
		(
			 IdContactEntreprise,
			 IdDomaineApplication,
			 TitreOffre,
			 DescriptionOffre,
			 CompetenceOffre,
			 PathPdfOffre,
			 NombrePosteOffre,
                         NombrePosteDispoOffre,
                         DateCreationOffre,
			 DateDebutOffre,
			 DateFinOffre,
			 StatutOffre,
			 TypeOffre
		)
		VALUES
		(
			:idCE,
			:idDA,
			:nom,
			:description,
			:competence,
			:path,
			:nbPoste,
                        :nbPosteDis,
                        :dateC,
			:dateD,
			:dateF,
			:statut,
			:type
		)';
        $sql->beginTransaction();
        try
        {
            $c = $sql->prepare($query);

            $c->bindParam(':idCE', $this->IdContactEntreprise, PDO::PARAM_INT);
            $c->bindParam(':idDA', $this->IdDomaineApplication, PDO::PARAM_INT);

            $c->bindParam(':nom', $this->Nom, PDO::PARAM_STR);
            $c->bindParam(':description', $this->Description, PDO::PARAM_STR);
            $c->bindParam(':competence', $this->Competence, PDO::PARAM_STR);
            $c->bindParam(':path', $this->PathPdf, PDO::PARAM_STR);

            $c->bindParam(':nbPoste', $this->NombrePoste, PDO::PARAM_INT);
            $c->bindParam(':nbPosteDis', $this->NombrePosteDispo, PDO::PARAM_INT);

            $c->bindParam(':dateC', time(), PDO::PARAM_INT);
            $c->bindParam(':dateD', $this->DateDebut, PDO::PARAM_INT);
            $c->bindParam(':dateF', $this->DateFin, PDO::PARAM_INT);

            $c->bindParam(':statut', $this->Statut, PDO::PARAM_STR);
            $c->bindParam(':type', $this->Type, PDO::PARAM_STR);

            if ($c->execute())
            {

                $this->Id = $sql->lastInsertId();

                // ON AJOUTE LES TECHNOS ASSOCIES
                $boolTechno = true;
                if (!empty($this->IdsTechnologie))
                {
                    $qTechno = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 3, :idTechno, :idOffre )";
                    foreach ($this->IdsTechnologie as $techno)
                    {
                        if ($boolTechno === false)
                        {
                            break;
                        }
                        if (!empty($techno))
                        {
                            $cTechno = $sql->prepare($qTechno);
                            $cTechno->bindParam(":idTechno", $techno, PDO::PARAM_INT);
                            $cTechno->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                            if ($cTechno->execute())
                            {
                                $boolTechno = true;
                            }
                            else
                            {
                                $boolTechno = false;
                            }
                        }
                    }
                }

                if ($boolTechno === true)
                {

                    // ON AJOUTE LES CATEGORIES ASSOCIEES
                    $boolCat = true;
                    if (!empty($this->IdsCategorie))
                    {
                        $qCat = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 4, :idCat, :idOffre )";
                        foreach ($this->IdsCategorie as $cat)
                        {
                            if ($boolCat === false)
                            {
                                break;
                            }
                            if (!empty($cat))
                            {
                                $cCat = $sql->prepare($qCat);
                                $cCat->bindParam(":idCat", $cat, PDO::PARAM_INT);
                                $cCat->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                                if ($cCat->execute())
                                {
                                    $boolCat = true;
                                }
                                else
                                {
                                    $boolCat = false;
                                }
                            }
                        }
                    }
                }

                if ($boolTechno === true && $boolCat === true)
                {
                    // ON AJOUTE LES PROMOS ASSOCIEES
                    $boolProm = true;
                    if (!empty($this->IdsPromo))
                    {
                        $qProm = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 1, :idProm, :idOffre )";
                        foreach ($this->IdsPromo as $prom)
                        {
                            settype($prom, "integer");
                            if ($boolProm === false)
                            {
                                break;
                            }
                            if (!empty($prom))
                            {
                                $cProm = $sql->prepare($qProm);
                                $cProm->bindParam(":idProm", $prom, PDO::PARAM_INT);
                                $cProm->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                                if ($cProm->execute())
                                {
                                    $boolProm = true;
                                }
                                else
                                {
                                    $boolProm = false;
                                }
                            }
                        }
                    }


                    if ($boolProm === true)
                    {
                        $sql->commit();
                        $c->closeCursor();
                        $this->requeteOk = true;
                        $this->errormsg = "";
                    }
                    else
                    {
                        $this->requeteOk = false;
                        $this->errormsg = "execute prom " . $c->errorInfo();
                        $sql->rollback();
                        $c->closeCursor();
                        $this->Id = 0;
                    }
                }
                else
                {
                    $this->requeteOk = false;
                    $this->errormsg = "execute techno " . $c->errorCode();
                    $sql->rollback();
                    $c->closeCursor();
                    $this->Id = 0;
                }
            }
            else
            {
                $this->requeteOk = false;
                $this->errormsg = "execute insert offre " . $c->errorInfo();
                $sql->rollback();
                $this->Id = 0;
            }
        }
        catch (PDOException $Exception)
        {
            $this->requeteOk = false;
            $this->errormsg = "Catch " . $c->errorInfo();
            $sql->rollback();
            $this->Id = 0;
        }

        $c->closeCursor();
    }

    /**
     * Met à jour l'offre dans la base de donnee
     * ainsi que toutes ces relations avec Technologies et Categorie
     * @see Categorie
     * @see Technologie
     * @see DomaineApplication
     */
    public function updateOffre()
    {
        //Recuperation des variables globales
        global $global_dsn;
        global $global_dsnUser;
        global $global_dsnPwd;
        //Instanciation connexion PDO
        $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
        $query =
                'UPDATE Offre
		SET 
			IdContactEntreprise 	= :idCE,
			IdDomaineApplication 	= :idDA,
			TitreOffre 				= :nom,
			DescriptionOffre 		= :description,
			CompetenceOffre 		= :competence,
			PathPdfOffre			= :path,
			NombrePosteOffre		= :nbPoste,
                        NombrePosteDispoOffre           = :nbPosteDis,
			DateDebutOffre			= :dateD,
			DateFinOffre			= :dateF,
			StatutOffre				= :statut,
			TypeOffre				= :type
		WHERE 
			IdOffre 				= :id';
        $sql->beginTransaction();
        try
        {
            $c = $sql->prepare($query);

            $c->bindParam(':id', $this->Id, PDO::PARAM_INT);
            $c->bindParam(':idCE', $this->IdContactEntreprise, PDO::PARAM_INT);
            $c->bindParam(':idDA', $this->IdDomaineApplication, PDO::PARAM_INT);

            $c->bindParam(':nom', $this->Nom, PDO::PARAM_STR);
            $c->bindParam(':description', $this->Description, PDO::PARAM_STR);
            $c->bindParam(':competence', $this->Competence, PDO::PARAM_STR);
            $c->bindParam(':path', $this->PathPdf, PDO::PARAM_STR);

            $c->bindParam(':nbPoste', $this->NombrePoste, PDO::PARAM_INT);
            $c->bindParam(':nbPosteDis', $this->NombrePosteDispo, PDO::PARAM_INT);

            $c->bindParam(':dateD', $this->DateDebut, PDO::PARAM_INT);
            $c->bindParam(':dateF', $this->DateFin, PDO::PARAM_INT);

            $c->bindParam(':statut', $this->Statut, PDO::PARAM_STR);
            $c->bindParam(':type', $this->Type, PDO::PARAM_STR);

            if ($c->execute())
            {
                // EN PREMIER ON SUPPRIME TOUTES LES RELATIONS DE L'OFFRE VERS SES CATEGORIES OU TECHNOS
                $qDelete = "DELETE FROM CritereOffreRS WHERE IdOffre = :id ;";
                $cDelete = $sql->prepare($qDelete);
                $cDelete->bindParam(":id", $this->Id, PDO::PARAM_INT);
                $cDelete->execute();

                // ON AJOUTE LES TECHNOS ASSOCIES
                $boolTechno = true;
                if (!empty($this->IdsTechnologie))
                {
                    $qTechno = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 3, :idTechno, :idOffre )";
                    foreach ($this->IdsTechnologie as $techno)
                    {
                        if ($boolTechno === false)
                        {
                            break;
                        }
                        if (!empty($techno))
                        {
                            $cTechno = $sql->prepare($qTechno);
                            $cTechno->bindParam(":idTechno", $techno, PDO::PARAM_INT);
                            $cTechno->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                            if ($cTechno->execute())
                            {
                                $boolTechno = true;
                            }
                            else
                            {
                                $boolTechno = false;
                            }
                        }
                    }
                }

                $boolCat = true;
                if ($boolTechno === true)
                {

                    // ON AJOUTE LES CATEGORIES ASSOCIEES
                    if (!empty($this->IdsCategorie))
                    {
                        $qCat = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 4, :idCat, :idOffre )";
                        foreach ($this->IdsCategorie as $cat)
                        {
                            settype($cat, "integer");
                            if ($boolCat === false)
                            {
                                break;
                            }
                            if (!empty($cat))
                            {
                                $cCat = $sql->prepare($qCat);
                                $cCat->bindParam(":idCat", $cat, PDO::PARAM_INT);
                                $cCat->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                                if ($cCat->execute())
                                {
                                    $boolCat = true;
                                }
                                else
                                {
                                    $boolCat = false;
                                }
                            }
                        }
                    }
                }

                $boolProm = true;
                if ($boolTechno === true && $boolCat === true)
                {
                    // ON AJOUTE LES PROMOS ASSOCIEES
                    if (!empty($this->IdsPromo))
                    {
                        $qProm = "INSERT INTO CritereOffreRS
                                    ( IdTypeCritere, IdCritere, IdOffre )
                                VALUES
                                    ( 1, :idProm, :idOffre )";
                        foreach ($this->IdsPromo as $prom)
                        {
                            settype($prom, "integer");
                            if ($boolProm === false)
                            {
                                break;
                            }
                            if (!empty($prom))
                            {
                                $cProm = $sql->prepare($qProm);
                                $cProm->bindParam(":idProm", $prom, PDO::PARAM_INT);
                                $cProm->bindParam(":idOffre", $this->Id, PDO::PARAM_INT);
                                if ($cProm->execute())
                                {
                                    $boolProm = true;
                                }
                                else
                                {
                                    $boolProm = false;
                                }
                            }
                        }
                    }
                }
                else
                {
                    $this->requeteOk = false;
                    $this->errormsg = $c->errorInfo();
                    $sql->rollback();
                    $c->closeCursor();
                }

                if ($boolCat === true && $boolTechno === true && $boolProm === true)
                {
                    $sql->commit();
                    $c->closeCursor();
                    $this->requeteOk = true;
                    $this->errormsg = "";
                }
                else
                {
                    $this->requeteOk = false;
                    $this->errormsg = $c->errorInfo();
                    $sql->rollback();
                    $c->closeCursor();
                }
            }
            else
            {
                $this->requeteOk = false;
                $this->errormsg = $c->errorInfo();
                $sql->rollback();
                $c->closeCursor();
            }
        }
        catch (PDOException $Exception)
        {
            $this->requeteOk = false;
            $this->errormsg = $c->errorInfo();
            echo '<pre>';
            var_dump($this);
            echo '</pre>';
            $sql->rollback();
            $c->closeCursor();
        }
    }

    /**
     * Enleve un poste à l'offre
     * Si l'offre possede plus qu'une place on en enleve une
     * Sinon l'offre passe a convoitee
     */
    public function addFicheValide()
    {
        if ($this->NombrePosteDispo <= 1)
        {
            $this->NombrePosteDispo = 0;
            $this->Statut = "convoitee";
        }
        else if ($this->NombrePosteDispo > 1)
        {
            $this->NombrePosteDispo = $this->NombrePosteDispo - 1;
        }
    }

    /**
     * Renvoi la liste des offres du portail (pas les fiches de stage)
     * @static
     * @param int $limit
     * @param int $begin
     * @return mixed Array d'offre
     */
    static public function getListOffre($limit=0, $begin=0, $StatutOffre="")
    {
        if (empty($limit))
        {
            $sql = SQL::getInstance()->getConnection();
            $query = 'SELECT Offre.* FROM Offre WHERE Offre.TypeOffre = "portail"';
            $c = $sql->query($query);
            $result = $c->fetchAll(PDO::FETCH_OBJ);
            return $result;
        }
        else
        {
            $sql = SQL::getInstance()->getConnection();
            $query = 'SELECT Offre.* FROM Offre WHERE Offre.TypeOffre = "portail"';
            if (!empty($StatutOffre)) {
                $query .= ' AND Offre.StatutOffre = :StatutOffre';
            }
            $query .= ' LIMIT :begin,:limit';
 
            $c = $sql->prepare($query);
            $c->bindParam(':begin', $begin, PDO::PARAM_INT);
            $c->bindParam(':limit', $limit, PDO::PARAM_INT);
            if (!empty($StatutOffre)) {
                $c->bindParam(":StatutOffre", $StatutOffre, PDO::PARAM_STR);
            }

            if ($c->execute())
            {
                $result = $c->fetchAll(PDO::FETCH_OBJ);
                return $result;
            }
            else
            {
                return array();
            }
        }
    }

    /**
     * Renvoie la liste des offres de la plus recente a la plus ancienne
     * pouvant être affichées sur le portail, c'est-à-dire les offres
     * où il reste de la place et de type "portail"
     * @static
     * @param int $limit
     * @param int $begin
     * @return mixed Array d'offre
     */
    static public function getListPortailOffreDesc($limit=0, $begin=0)
    {
        if (empty($limit))
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = '
                    SELECT Offre.*
                    FROM Offre
                    WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"
                    ORDER BY Offre.DateCreationOffre DESC
                ';
            $c = $sql->query($query);
            $result = $c->fetchAll(PDO::FETCH_OBJ);
            return $result;
        }
        else
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = 'SELECT Offre.*
                    FROM Offre
                    WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"
                    ORDER BY Offre.DateCreationOffre DESC';
            $query .= ' LIMIT :begin,:limit';
            $c = $sql->prepare($query);
            $c->bindParam(':begin', $begin, PDO::PARAM_INT);
            $c->bindParam(':limit', $limit, PDO::PARAM_INT);

            if ($c->execute())
            {
                $result = $c->fetchAll(PDO::FETCH_OBJ);
                return $result;
            }
            else
            {
                return array();
            }
        }
    }

    static public function getListOffrePortailByCritere($limit=0, $begin=0,$categories = array(), $domaines = array(), $technos = array(),$IdsPromo=array())
    {
        if(!empty($limit))
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = 'SELECT Offre.* FROM Offre';
            if(!empty($categories))
                $query .=' LEFT JOIN CritereOffreRS a ON a.IdOffre = Offre.IdOffre';
            if(!empty($domaines))
                $query .=' LEFT JOIN CritereOffreRS b ON b.IdOffre = Offre.IdOffre';
            if(!empty($technos))
                $query .=' LEFT JOIN CritereOffreRS c ON c.IdOffre = Offre.IdOffre';
            if(!empty($IdsPromo))
                $query .=' LEFT JOIN CritereOffreRS d ON d.IdOffre = Offre.IdOffre';
            $query .=' WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"';
            if(!empty($categories) || !empty($domaines) || !empty($technos) || !empty($IdsPromo))
                $query .= " AND (";
            if(!empty($categories))
            {
               $query .= " ( a.IdTypeCritere = 4 AND (";
               foreach($categories as $i)
               {
                    $query .= " a.IdCritere = ".$i." ||";
               }
               $query = substr($query, 0,-2);
               $query .= " ) )";
            }

            if(!empty($domaines))
            {
                if(!empty($categories))
                    $query .= "AND ";
               $query .= " ( b.IdTypeCritere = 5 AND (";
               foreach($domaines as $i)
               {
                    $query .= " b.IdCritere = ".$i." ||";
               }
               $query = substr($query, 0,-2);
               $query .= " ) )";
            }
            if(!empty($technos))
            {
                if(!empty($categories) || !empty($domaines))
                    $query .= "AND ";
               $query .= " ( c.IdTypeCritere = 3 AND (";
               foreach($technos as $i)
               {
                    $query .= " c.IdCritere = ".$i." ||";
               }
               $query = substr($query, 0,-2);
               $query .= " ) )";
            }
            if(!empty($IdsPromo))
            {
                if(!empty($categories) || !empty($domaines) || !empty($technos))
                    $query .= "AND ";
               $query .= " ( d.IdTypeCritere = 1 AND (";
               foreach($IdsPromo as $i)
               {
                    $query .= " d.IdCritere = ".$i." ||";
               }
               $query = substr($query, 0,-2);
               $query .= " ) )";
            }

            if(!empty($categories) || !empty($domaines) || !empty($technos) || !empty($IdsPromo))
                $query .= " )";
            $query .= " GROUP BY Offre.IdOffre";
            $query .= ' ORDER BY Offre.DateCreationOffre DESC';
            $query .= ' LIMIT :begin,:limit';
            
            // $query;
            $c = $sql->prepare($query);
            $c->bindParam(':begin', $begin, PDO::PARAM_INT);
            $c->bindParam(':limit', $limit, PDO::PARAM_INT);

            if ($c->execute())
            {
                $result = $c->fetchAll(PDO::FETCH_OBJ);
                return $result;
            }
        }
        return array();
    }

    /**
     * Renvoie la liste des offres pouvant être affichées sur le portail, c'est-à-dire les offres
     * où il reste de la place et de type "portail"
     * @static
     * @param int $limit
     * @param int $begin
     * @return mixed Array d'offre
     */
    static public function getListPortailOffre($limit=0, $begin=0)
    {
        if (empty($limit))
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = '
                    SELECT Offre.*
                    FROM Offre
                    WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"
                ';
            $c = $sql->query($query);
            $result = $c->fetchAll(PDO::FETCH_OBJ);
            return $result;
        }
        else
        {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = 'SELECT Offre.*
                    FROM Offre
                    WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"';
            $query .= ' LIMIT :begin,:limit';
            $c = $sql->prepare($query);
            $c->bindParam(':begin', $begin, PDO::PARAM_INT);
            $c->bindParam(':limit', $limit, PDO::PARAM_INT);

            if ($c->execute())
            {
                $result = $c->fetchAll(PDO::FETCH_OBJ);
                return $result;
            }
            else
            {
                return array();
            }
        }
    }

    /**
     * @static
     * @return int Nombre total d'offre affichable en public
     */
    static public function affichageCount()
    {
        global $global_dsn;
        global $global_dsnUser;
        global $global_dsnPwd;
        //Instanciation connexion PDO
        $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);

        $query = '  SELECT COUNT(IdOffre) as count
                    FROM Offre
                    WHERE   (Offre.StatutOffre = "acceptee"
                            OR
                            Offre.StatutOffre = "convoitee")
                            AND
                            Offre.NombrePosteDispoOffre > 0
                            AND
                            Offre.TypeOffre = "portail"';
        $c = $sql->query($query);
        $result = $c->fetch(PDO::FETCH_OBJ);
        return $result->count;
    }

    /**
     * Compte les offres du portail (pas les fiches de stage)
     * @static
     * @return int Nombre total d'offre
     */
    static public function globalCount($StatutOffre)
    {
        if (empty($StatutOffre)) {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            //Instanciation connexion PDO
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);

            $query = 'SELECT COUNT(IdOffre) as count FROM Offre WHERE Offre.TypeOffre = "portail"';
            $c = $sql->query($query);
            $result = $c->fetch(PDO::FETCH_OBJ);
            return $result->count;
        }
        else {
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            //Instanciation connexion PDO
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);

            $query = 'SELECT COUNT(IdOffre) as count FROM Offre WHERE Offre.TypeOffre = "portail" AND Offre.StatutOffre = :StatutOffre';
            $c = $sql->prepare($query);
            $c->bindParam(":StatutOffre", $StatutOffre, PDO::PARAM_STR);
            $c->execute();
            $result = $c->fetch(PDO::FETCH_OBJ);
            return $result->count;
        }
    }

    /**
     *
     * @param int $id
     * @return Array d'offre
     */
    static public function getListOffreByNom($id)
    {
        global $global_dsn, $global_dsnUser, $global_dsnPwd;
        $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);

        $query = "SELECT * FROM Offre WHERE IdContactEntreprise = :id";

        $c = $sql->prepare($query);
        $c->bindParam(':id', $id, PDO::PARAM_INT);
        if ($c->execute())
        {
            $result = $c->fetchAll(PDO::FETCH_OBJ);
            return $result;
        }
        else
        {
            return array();
        }
    }
    

    /**
     *
     * @param int $id
     * @return mixed Array d'offre
     */
    static public function getOffresByIdOffre($id)
    {

        if (isset($id) && !empty($id))
        {
            //Recuperation des variables globales
            global $global_dsn;
            global $global_dsnUser;
            global $global_dsnPwd;
            //Instanciation connexion PDO
            $sql = new PDO($global_dsn, $global_dsnUser, $global_dsnPwd);
            $query = "SELECT o.* , e.NomEntreprise, d.NomDomaineApplication
                          FROM Offre o
                          INNER JOIN ContactEntreprise c ON c.idContactEntreprise = o.IdContactEntreprise
                          INNER JOIN Entreprise e ON e.IdEntreprise = c.IdEntreprise
                          INNER JOIN DomaineApplication d ON o.IdDomaineApplication = d.idDomaineApplication
                          WHERE o.IdOffre = :id";
            $c = $sql->prepare($query);
            $c->bindParam(":id", $id, PDO::PARAM_INT);
            $c->execute();
            $result = $c->fetch(PDO::FETCH_OBJ);
            if (!empty($result))
            {
                return $result;
            }
            else {
                return "";
            }
        }
    }

    /**
     *
     * @return int
     */
    public function getIdOffre()
    {
        return $this->Id;
    }

    /**
     *
     * @return int
     */
    public function getIdContactEntreprise()
    {
        return $this->IdContactEntreprise;
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setIdContactEntreprise($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->IdContactEntreprise = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "L'IdContactEntreprise ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "L'IdContactEntreprise ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cet IdContactEntreprise n'est pas valide !";
            }

            $this->IdContactEntreprise = 0;
        }
        return $errTab;
    }

    /**
     *
     * @return int
     */
    public function getIdDomaineApplication()
    {
        return $this->IdDomaineApplication;
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setIdDomaineApplication($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->IdDomaineApplication = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "L'IdContactEntreprise ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "L'IdContactEntreprise ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cet IdContactEntreprise n'est pas valide !";
            }

            $this->IdDomaineApplication = 0;
        }
        return $errTab;
    }

    /**
     *
     * @return mixed
     */
    public function getIdsCategorie()
    {
        return $this->IdsCategorie;
    }

    /**
     *
     * @return mixed
     */
    public function getIdsTechnologie()
    {
        return $this->IdsTechnologie;
    }
	/**
     *
     * @return mixed
     */
	public function getIdsPromo()
    {
        return $this->IdsPromo;
    }

    /**
     *
     * @param mixed $value
     * @return mixed
     */
    public function setIdsCategorie($array)
    {
        $errTab = array("boolOk" => true, "errmsg" => "");
        if (isset($array) && is_array($array))
        {
            $this->IdsCategorie = $array;
        }
        else
        {
            $errTab["boolOk"] = false;
            $errTab["errmsg"] = "Merci d'envoyer un array ;)";
        }
        return $errTab;
    }

    /**
     *
     * @param mixed $value
     * @return mixed
     */
    public function setIdsTechnologie($array)
    {
        $errTab = array("boolOk" => true, "errmsg" => "");
        if (isset($array) && is_array($array))
        {
            $this->IdsTechnologie = $array;
        }
        else
        {
            $errTab["boolOk"] = false;
            $errTab["errmsg"] = "Merci d'envoyer un array ;)";
        }
        return $errTab;
    }

    /**
     *
     * @param mixed $value
     * @return mixed
     */
    public function setIdsPromo($array)
    {
        $errTab = array("boolOk" => true, "errmsg" => "");
        if (isset($array) && is_array($array))
        {
            $this->IdsPromo = $array;
        }
        else
        {
            $errTab["boolOk"] = false;
            $errTab["errmsg"] = "Merci d'envoyer un array ;)";
        }
        return $errTab;
    }

    public function getIdPromo()
    {
        return $this->IdsPromo[0];
    }

    /**
     *
     * @param int $id
     * @return mixed
     */
    public function addCategorie($id)
    {
        $errTab = array("boolOk" => true, "errmsg" => "");
        if (isset($id) && !empty($id))
        {
            // Test si id present ou non
            if (array_search($id, $this->IdsCategorie) === false)
            {
                array_push($this->IdsCategorie, $id);
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cet id est deja present ;)";
            }
        }
        else
        {
            $errTab["boolOk"] = false;
            $errTab["errmsg"] = "Merci d'envoyer un id ;)";
        }
        return $errTab;
    }

    /**
     *
     * @param int $id
     * @return mixed
     */
    public function addTechnologie($id)
    {
        $errTab = array("boolOk" => true, "errmsg" => "");
        if (isset($id) && !empty($id))
        {
            // Test si id present ou non
            if (array_search($id, $this->IdsTechnologie) === false)
            {
                array_push($this->IdsTechnologie, $id);
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cet id est deja present ;)";
            }
        }
        else
        {
            $errTab["boolOk"] = false;
            $errTab["errmsg"] = "Merci d'envoyer un id ;)";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getTitreOffre()
    {
        return $this->Nom;
    }

    /**
     *
     * @param string $value
     * @return mixed
     */
    public function setTitreOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "" && strlen($value) <= 255)
        {
            $this->Nom = htmlentities($value);
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nom ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nom ne peut etre vide !";
            }
            else
            {
                if (isset($value) && strlen($value) > 255)
                {
                    $errTab["boolOk"] = false;
                    $errTab["errmsg"] = "Le nom doit etre compris entre 1 et 255 caracteres !";
                }
                else
                {
                    $errTab["boolOk"] = false;
                    $errTab["errmsg"] = "Ce nom n'est pas valide !";
                }
            }

            $this->Nom = "";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getDescriptionOffre()
    {
        return $this->Description;
    }

    /**
     * 
     * @param string $value
     * @return mixed
     */
    public function setDescriptionOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "")
        {
            $this->Description = htmlentities($value);
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La description ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La description ne peut etre vide !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cette description n'est pas valide !";
            }

            $this->Description = "";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getCompetenceOffre()
    {
        return $this->Competence;
    }

    /**
     *
     * @param string $value
     * @return mixed
     */
    public function setComptenceOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "")
        {
            $this->Competence = htmlentities($value);
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La competence ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La competence ne peut etre vide !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cette competence n'est pas valide !";
            }

            $this->Competence = "";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getPathPdfOffre()
    {
        return $this->PathPdf;
    }

    /**
     *
     * @param string $value
     * @return mixed
     */
    public function setPathPdfOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "" && strlen($value) <= 255)
        {
            $this->PathPdf = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le path ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le path ne peut etre vide !";
            }
            else
            {
                if (isset($value) && strlen($value) > 255)
                {
                    $errTab["boolOk"] = false;
                    $errTab["errmsg"] = "Le path doit etre compris entre 1 et 255 caracteres !";
                }
                else
                {
                    $errTab["boolOk"] = false;
                    $errTab["errmsg"] = "Ce path n'est pas valide !";
                }
            }

            $this->PathPdf = "";
        }
        return $errTab;
    }

    /**
     *
     * @return int
     */
    public function getNombrePosteOffre()
    {
        return $this->NombrePoste;
    }

    /**
     *
     * @return int
     */
    public function getNombrePosteDispoOffre()
    {
        return $this->NombrePosteDispo;
    }
    
    /**
     * Enleve un poste dispo à l'offre
     * @return boolean
     */
    public function enleveUnPoste()
    {
        if($this->NombrePosteDispo > 0)
        {
            $this->NombrePosteDispo -= 1;
            return true;
        }
        else
        {
            return false;
        }
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setNombrePosteDispoOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->NombrePosteDispo = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nombre de poste ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nombre de poste ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Ce nombre de poste n'est pas valide !";
            }

            $this->NombrePosteDispo = 0;
        }
        return $errTab;
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setNombrePosteOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->NombrePoste = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nombre de poste ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le nombre de poste ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Ce nombre de poste n'est pas valide !";
            }

            $this->NombrePoste = 0;
        }
        return $errTab;
    }

    /**
     *
     * @return int
     */
    public function getDateDebutOffre()
    {
        return $this->DateDebut;
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setDateDebutOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->DateDebut = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La date de debut ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La date de debut ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cette date de debut n'est pas valide !";
            }

            $this->DateDebut = 0;
        }
        return $errTab;
    }

    /**
     *
     * @return int
     */
    public function getDateFinOffre()
    {
        return $this->DateFin;
    }

    /**
     *
     * @param int $value
     * @return mixed
     */
    public function setDateFinOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value > 0)
        {
            $this->DateFin = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La date de fin ne peut etre null !";
            }
            if (isset($value) && $value <= 0)
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "La date de fin ne peut etre <= 0 !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Cette date de fin n'est pas valide !";
            }

            $this->DateFin = 0;
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getStatutOffre()
    {
        return $this->Statut;
    }

    /**
     * Renvoie un affichage correct du statut de l'offre
     * @return string
     */
    public function getStatutPublicOffre()
    {
        switch($this->Statut)
        {
            case "acceptee":
                return "Ouvert";
                break;
            case "convoitee":
                return "Convoit&eacute;e";
                break;
        }
    }

    /**
     *
     * @param string $value
     * @return mixed
     */
    public function setStatutOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "")
        {
            $this->Statut = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le statut ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le statut ne peut etre vide !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Ce statut n'est pas valide !";
            }

            $this->Statut = "";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getTypeOffre()
    {
        return $this->Type;
    }

    /**
     *
     * @param string $value
     * @return mixed
     */
    public function setTypeOffre($value)
    {
        //Verif Type + verif Nulle, si bon mis a jour, sinon message erreur
        $errTab = array("boolOk" => true, "errmsg" => "");

        if (isset($value) && $value != "")
        {
            $this->Type = $value;
        }
        else
        {
            if (!isset($value))
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le type ne peut etre null !";
            }
            if (isset($value) && $value == "")
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Le type ne peut etre vide !";
            }
            else
            {
                $errTab["boolOk"] = false;
                $errTab["errmsg"] = "Ce type n'est pas valide !";
            }

            $this->Type = "";
        }
        return $errTab;
    }

    /**
     *
     * @return string
     */
    public function getDateCreationOffre()
    {
        return $this->DateCreation;
    }

}

?>